package com.example.demo1.dao;

import com.example.demo1.entity.Book;
import com.example.demo1.util.JDBCUtil;
import com.example.demo1.util.StringUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * 预编译语句（PreparedStatement） 防止 SQL 注入
 * try-with-resources 会自动关闭 connection
 * SQL 插入语句，? 是占位符，对应后面的参数
 * 创建预编译语句对象，SQL 已发送到数据库预编译
 * 设置占位符参数（从 1 开始索引）
 * 执行更新操作，返回受影响的行数（插入成功返回 1）
 * PreparedStatement 未在 try() 中声明，但会随 connection 关闭而自动关闭
 * 获取连接 → 2. 创建预编译 SQL → 3. 设置参数 → 4. 执行更新 → 5. 自动关闭资源。
 * */

/**
 * 这个类仅出现sql语句，不要出现输入输出流
 * 这里会将这些信息通过对象进行传递
 * */
public class BookDaoImpl implements BookDao {


    @Override
    public int addBook(Book book) {
        try(Connection connection = JDBCUtil.getConnection()){
            String sql = "insert into book(ID,name,author,date,press,price,statu) values(?,?,?,?,?,?,?)";
            PreparedStatement preSta = connection.prepareStatement(sql);
            preSta.setString(1,book.getID());
            preSta.setString(2,book.getName());
            preSta.setString(3,book.getAuthor());
            preSta.setString(4,book.getDate());
            preSta.setString(5,book.getPress());
            preSta.setString(6,book.getPrice());
            preSta.setString(7,book.getStatu());
            int num = preSta.executeUpdate();
            return num;
        }catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public int deleteBook(Serializable id) {
        try(Connection conn = JDBCUtil.getConnection()){
            String sql = "delete from book where ID = ?";
            PreparedStatement preSta = conn.prepareStatement(sql);
            preSta.setObject(1,id);
            int num = preSta.executeUpdate();
            return num;
        }catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public int updateBook(Book book) {
        try(Connection conn = JDBCUtil.getConnection()){
            ArrayList<Object> list = new ArrayList<>();
            StringBuffer sql = new StringBuffer("update book set ");
            boolean newWords = false;


            if(StringUtil.isNotEmpty(book.getName())){
                sql.append(" name = ?, ");
                list.add(book.getName());
                newWords = true;
            }
            if(StringUtil.isNotEmpty(book.getAuthor())){
                sql.append(" author = ?, ");
                list.add(book.getAuthor());
                newWords = true;
            }
            if(StringUtil.isNotEmpty(book.getDate())){
                sql.append(" date = ?, ");
                list.add(book.getDate());
                newWords = true;
            }
            if(StringUtil.isNotEmpty(book.getPress())){
                sql.append(" press = ?, ");
                list.add(book.getPress());
                newWords = true;
            }
            if(book.getPrice() != null){
                sql.append(" price = ?, ");
                list.add(book.getPrice());
                newWords = true;
            }
            if(book.getStatu() != null){
                sql.append(" statu = ? ");
                list.add(book.getStatu());
                newWords = true;
            }

            if(!newWords){
                System.out.println("无更新字段");
                return 0;
            }

            sql.append(" where ID = ? ");
            list.add(book.getID());
            PreparedStatement preSta = conn.prepareStatement(sql.toString());
            for(int i = 0;i<list.size();i++){
                preSta.setObject(i+1,list.get(i));
            }
            return preSta.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public Book selectBook(Serializable id) {
        try(Connection conn = JDBCUtil.getConnection()){
            String sql ="select * from book where ID = ?";
            PreparedStatement preSta = conn.prepareStatement(sql);
            preSta.setObject(1,id);
            ResultSet rs =  preSta.executeQuery();
            if(rs.next()){
                String bookId = rs.getString("ID");
                String name = rs.getString("name");
                String author = rs.getString("author");
                String date = rs.getString("date");
                String press = rs.getString("press");
                String price = rs.getString("price");
                String status = rs.getString("statu");
                Book book = new Book(bookId,name,author,date,press,price,status);
                return book;
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public ArrayList<Book> selectBooks(Book book) {
        ArrayList <Book> list = new ArrayList<>();
        try(Connection coon = JDBCUtil.getConnection()){
            StringBuffer sql = new StringBuffer( "select * from book where 1=1");
            ArrayList<Object> params = new ArrayList<>();
            if(StringUtil.isNotEmpty(book.getName())){
                sql.append(" and name like ?");
                params.add("%" + book.getName() + "%");
            }

            if(StringUtil.isNotEmpty(book.getAuthor())){
                sql.append(" and author like ?");
                params.add("%" + book.getAuthor() + "%");
            }

            PreparedStatement preSta = coon.prepareStatement(sql.toString());
            for(int i = 0;i < params.size();i++){
                preSta.setObject(i+1,params.get(i));
            }
            //执行查询
            ResultSet rs = preSta.executeQuery();
            while(rs.next()) {
                String bookId = rs.getString("ID");
                String name = rs.getString("name");
                String author = rs.getString("author");
                String date = rs.getString("date");
                String press = rs.getString("press");
                String price = rs.getString("price");
                String status = rs.getString("statu");
                Book bk = new Book(bookId,name,author,date,press,price,status);
                list.add(bk);
            }
        }catch (Exception e){
            e.printStackTrace();
        }


        return list;
    }
}